SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchAndReplaceText]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[SearchAndReplaceText] 
(
@otxt varchar(100),
@ntxt varchar(100)

)

AS

declare curs cursor local fast_forward
for
select 
	Contentid,
	textptr(Url),
	charindex(@otxt, Url)-1
from 
	Content 
where 
	Url 
like 
	''%'' + @otxt +''%''

declare @txtlen int
set @txtlen = len(@otxt)

declare @ptr binary(16)
declare @pos int
declare @id int

open curs

fetch next from curs into @id, @ptr, @pos

while @@fetch_status = 0
begin	
	updatetext Content.URL @ptr @pos @txtlen @ntxt

	fetch next from curs into @id, @ptr, @pos	
end

close curs
deallocate curs' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Pages_GetList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetHeadlines    Script Date: 16/12/2003 2:36:38 PM ******/
CREATE PROCEDURE [dbo].[Pages_GetList]
(
@ProjectId int
)
AS
	SET NOCOUNT ON
	
      SELECT * from Pages
where Project=@ProjectId
order by Pages.PageName

	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Template_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Template_Insert]
(
@TemplateName varchar(50),
@ProjectId int
)
AS 
INSERT  INTO Templates
(TemplateName, Project) 
VALUES
(@TemplateName, @ProjectId)' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Projects]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Projects](
	[ProjectId] [int] NOT NULL,
	[Name] [varchar](200) NOT NULL,
	[Url] [varchar](300) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Template_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure Template_Delete    Script Date: 24/08/2003 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Template_Delete]
	(
		@TemplateID INT = NULL
	)
AS
    DELETE FROM Templates
    
    WHERE TemplateId=@TemplateID
	
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Template_ElementInsertWithOrder]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Template_ElementInsertWithOrder]
(
@TemplateId as int=NULL,
@ElementID as int=NULL,
@ElementName varchar(50),
@ElementCode text,
@ElementOrder int
)
AS 
INSERT  INTO Template_Elts 
(TemplateId,TemplateEltId, Type_T_Elt, Eltcode, EltOrder) 
VALUES
(@TemplateID, @ElementID, @ElementName, @ElementCode,@ElementOrder)' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Template_GetHTML]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Template_GetHTML    Script Date: 10/5/2001 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Template_GetHTML]
(

@TemplateID Int =NULL
)

AS
	SET NOCOUNT ON
	
SELECT     TemplateCode
FROM         Templates 
WHERE    Templates.TemplateId= @TemplateID

	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Template_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Template_Update]
(
@TemplateId as int=NULL,
@TemplateName varchar(50),
@TemplateCode as text
)
AS 
UPDATE Templates
SET 
TemplateCode=@Templatecode,
TemplateName=@TemplateName

where TemplateID=@TemplateId' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Template_GetElementHTML]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Template_GetElementHTML    Script Date: 25/08/2003 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Template_GetElementHTML]
(	
@TemplateID int = NULL,
@elementID Int =NULL,
@TypeElt varchar(50)
)

AS
	SET NOCOUNT ON
	
SELECT     eltcode
FROM         Template_Elts 
WHERE    Template_Elts.TemplateEltID= @elementID
AND Type_T_Elt=@TypeElt
AND TemplateID=@TemplateID

	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Template_ElementUpdate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Template_ElementUpdate]
(
@TemplateId as int,
@ElementID as  int,
@ElementName varchar(200),
@ElementCode as text
)
AS 
UPDATE Template_Elts 
SET
Eltcode=@ElementCode
WHERE    TemplateEltID= @elementID
AND TemplateID=@TemplateId
AND Type_T_Elt=@ElementName' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sections_GetListVersions]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetHeadlines    Script Date: 10/5/2001 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Sections_GetListVersions]
(

@SectionID Int 
)

AS
	SET NOCOUNT ON
	SELECT DISTINCT 
                      SectionElements.SectionVersion, CONVERT(varchar, SectionElements.DateCreated, 103) AS Datecreation, SectionElements.Status, 
                      Account.UserName
FROM         SectionElements INNER JOIN
                      Account ON SectionElements.Author = Account.AccountID
WHERE    SectionId = @SectionID

	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchAndReplaceText_SectionContent]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[SearchAndReplaceText_SectionContent]
(
@otxt varchar(100),
@ntxt varchar(100)

)

AS

declare curs cursor local fast_forward
for
select 
	SectionContent.Id,
	textptr(sectioncontent),
	charindex(@otxt, sectioncontent)-1
from 
	SectionContent 
where 
	SectionContent 
like 
	''%'' + @otxt +''%''

declare @txtlen int
set @txtlen = len(@otxt)

declare @ptr binary(16)
declare @pos int
declare @id int

open curs

fetch next from curs into @id, @ptr, @pos

while @@fetch_status = 0
begin	
	updatetext SectionContent.sectioncontent @ptr @pos @txtlen @ntxt

	fetch next from curs into @id, @ptr, @pos	
end

close curs
deallocate curs' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sections_GetTemplateId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Sections_GetTemplateId   Script Date: 12/07/2003 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Sections_GetTemplateId]
(

@SectionID Int 
)

AS
	SET NOCOUNT ON
	
  SELECT      DISTINCT Template_Elts.Type_T_Elt + '' '' + CAST(Template_Elts.TemplateEltId AS varchar(50)) AS TmpEltID, eltOrder
FROM         Sections INNER JOIN
                      Template_Elts ON Sections.TemplateID = Template_Elts.TemplateId
WHERE    SectionId = @SectionID
order by EltOrder

	RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Library_MediaTypes]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Library_MediaTypes](
	[MedIaTypeId] [int] IDENTITY(1,1) NOT NULL,
	[Icon] [image] NULL,
	[Name] [varchar](50) NULL,
	[ProjectId] [int] NULL,
 CONSTRAINT [PK_Library_MediaTypes] PRIMARY KEY CLUSTERED 
(
	[MedIaTypeId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Template_ElementDeleteByID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure Template_ElementDelete    Script Date: 9/10/2003 10:36:38 AM ******/
CREATE PROCEDURE [dbo].[Template_ElementDeleteByID]
	(
		@TemplateID int,
		@ElementID int,
		@ElementName varchar(200)
	)
AS
    DELETE FROM Template_Elts 
    
    WHERE TemplateId=@TemplateID and TemplateEltID=@ElementID
and Type_T_Elt=@ElementName
	
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Types_GetList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetHeadlines    Script Date: 5/09/2003 8:36:38 AM ******/
CREATE PROCEDURE [dbo].[Types_GetList]
AS
	SET NOCOUNT ON
	
      SELECT TypeID, TypeValue
        FROM Types_params 
	
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Element_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Sections_GetTemplateId   Script Date: 12/07/2003 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Element_Update]
(

@SectionID Int,
@SectionElementID Int,
@SectionElementType varchar(50),
@SectionVersionID Int,
@ContentID int,
@ModifiedDate datetime
)

AS
 SET NOCOUNT ON

/** Check if the Element already exist **/
if exists(

select SectionElements.SectionelementID
From SectionElements
WHERE     (SectionElements.SectionID =@SectionID) AND (SectionElements.SectionelementID = @SectionelementID)
AND (SectionElements.SectionVersion = @SectionVersionID)
AND (SectionElementType=@SectionElementType)
)

	
 	UPDATE
	SectionElements
	SET
	ContentID=@ContentID,
	Datemodified=@ModifiedDate

	WHERE     (SectionElements.SectionID =@SectionID) AND (SectionElements.SectionelementID = @SectionelementID)
	AND (SectionElements.SectionVersion = @SectionVersionID)
	AND (SectionElementType=@SectionElementType)



else

/** New element to be inserted **/

	INSERT Into SectionElements
(
SectionID,
SectionElementID,
SectionElementType,
SectionVersion,
ContentId
)
VALUES
(
@SectionID,
@SectionElementID,
@SectionElementType,
@SectionVersionID,
@ContentID
)' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Pages_GetListtoApprove]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Pages_GetListtoApprove    Script Date: 18/08/2003 10:36:38 AM ******/
CREATE PROCEDURE [dbo].[Pages_GetListtoApprove]
(
@ProjectId int
)
AS
	SET NOCOUNT ON
	
    SELECT DISTINCT Pages.PageName, Sections.SectionName, Account.UserName, Sections.DateCreated, CONVERT(varchar, Pages.PageId) + ''@'' + CONVERT(varchar, 
                      Sections.SectionId) AS Aid
FROM           Sections INNER JOIN
                      Pages ON Sections.PageID = Pages.PageId INNER JOIN
                      Account ON Sections.Editor = Account.AccountID INNER JOIN
                      Versions ON Sections.SectionId = Versions.SectionId
WHERE     (Versions.Status = 8) and (Pages.Project=@ProjectId)
ORDER BY Pages.PageName, Sections.SectionName

	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Template_ElementInsert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Template_ElementInsert]
(
@TemplateId as int=NULL,
@ElementID as int=NULL,
@ElementName varchar(50),
@ElementCode text
)
AS 
INSERT  INTO Template_Elts 
(TemplateId,TemplateEltId, Type_T_Elt, Eltcode) 
VALUES
(@TemplateID, @ElementID, @ElementName, @ElementCode)' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Template_ElementDelete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure Template_ElementDelete    Script Date: 24/08/2003 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Template_ElementDelete]
	(
		@TemplateID INT = NULL
	)
AS
    DELETE FROM Template_Elts 
    
    WHERE TemplateId=@TemplateID
	
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Details_Element]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Details_Element] 
(
@Page_Name as varchar (70),
@Elt_Name as varchar (70)
)
AS 
SELECT     *
FROM         Elements INNER JOIN
                      List_Page_Elt ON Elements.Elt_Id = List_Page_Elt.Elt_Id INNER JOIN
                      Pages ON List_Page_Elt.Page_Id = Pages.Page_Id
WHERE     (Pages.Page_Name = @Page_Name) AND (Elements.Elt_name = @Elt_Name)
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Template_GetLastId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure Template_GetLastId   Script Date: 23/08/2003 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Template_GetLastId]
(
@TemplateId as integer =Null,
@TypeElt as varchar(50)
)
AS

    SET NOCOUNT ON

    SELECT TemplateEltId 
    FROM     Template_Elts

    WHERE Type_T_elt=@TypeElt and TemplateId=@TemplateId

    ORDER BY TemplateEltID DESC
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Details_Element_ById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Details_Element_ById] 
(
@Page_Id as int,
@Elt_Id as int,
@Elt_tmpl as int
)
AS 
if @Elt_tmpl>0
begin
SELECT     *
FROM         List_Page_Elt INNER JOIN
                      Elements ON List_Page_Elt.Elt_Id = Elements.Elt_Id
WHERE     (List_Page_Elt.Page_Id = @Page_Id) AND (List_Page_Elt.Elt_Tmp = @Elt_tmpl) and (List_Page_Elt.Elt_Id =@Elt_Id)
end
else
begin
SELECT     *
FROM         List_Page_Elt INNER JOIN
                      Elements ON List_Page_Elt.Elt_Id = Elements.Elt_Id
WHERE     (List_Page_Elt.Page_Id = @Page_Id) AND (List_Page_Elt.Elt_Id = @Elt_id)
end
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Section_create]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Section_create   Script Date: 17:17 10/09/2003 ******/
CREATE PROCEDURE [dbo].[Section_create]
	(
		@SectionID INT=NULL       ,
		@SectionElementID    INT = NULL,
		@VersionId    INT = NULL,
		@SectionElementType varchar(50),
		@Author INT,
		@Status INT
	)
AS
	


INSERT  INTO SectionElements 
(SectionID,SectionElementID,SectionElementType, SectionVersion, Author, Status) 
VALUES
(@SectionID, @SectionElementID, @SectionElementType, @VersionId, @Author, @Status)' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_Approve]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_Approve    Script Date: 10/09/2003 2:36:40 AM ******/
CREATE PROCEDURE [dbo].[Content_Approve]
	(
		@ContentID INT,
		@Status INT,
		@Editor int
	)
AS
	SET NOCOUNT ON

	UPDATE Content
	SET 
		  
		   Status       = @Status,
		 Editor = @Editor
	       
	WHERE  ContentID = @ContentID
	
	RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Account_Authenticated]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Account_Authenticated    Script Date: 14/12/2003 ******/
CREATE PROCEDURE [dbo].[Account_Authenticated]
	(
		@username varchar(50) ,
		@ProjectId varchar(200)
	)
AS
	SET NOCOUNT ON

/** An Administrator can have access to all projects so we check if the username has the Administrator role **/
IF EXISTS (
SELECT     AccountRoles.Role
FROM         Account FULL OUTER JOIN
                      AccountRoles ON Account.AccountID = AccountRoles.AccountID
WHERE     (Account.UserName = @Username) AND (AccountRoles.Role = ''Administrator'')
)
    
    SELECT Password 
      FROM Account 
     WHERE UserName=@username
     
ELSE

SELECT     Password
FROM         Account RIGHT OUTER JOIN
                      AccountProperty ON Account.AccountID = AccountProperty.AccountID
WHERE     (Account.UserName =@Username) AND (AccountProperty.Property = ''Project'') AND (AccountProperty.[value] = @ProjectId)

RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Image_GetProperties]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE  [dbo].[Image_GetProperties]
(
@ImageId Int,
@Language int
)
AS
select *
from Library
where ImageId=@ImageId and language=@language' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Account_Exist]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Account_Exist    Script Date: 10/5/2001 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Account_Exist]
	(
		@AccountID INT = NULL
	)
AS
	SET NOCOUNT ON
    SELECT AccountID 
      FROM Account 
     WHERE AccountID=@AccountID
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Account_GetAccounts]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Account_GetAccounts    Script Date: 10/5/2001 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Account_GetAccounts]
AS
	SET NOCOUNT ON
    Select   AccountID, UserName, Email, [Password]
    FROM     Account
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Image_GetFormat]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE  [dbo].[Image_GetFormat]
(
@ImageId Int
)
AS
select ContentType
from Library
where ImageId=@ImageId' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Section_UpdateContent]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Sections_GetTemplateId   Script Date: 14/09/2005 ******/
CREATE PROCEDURE [dbo].[Section_UpdateContent]
(
@SectionID Int,
@VersionId Int,
@SectionContent text,
@SectionContent_lang2  text,
@TextOnly text
)
AS
 SET NOCOUNT ON
/** Check if the Element already exist **/
/** V 2.0 Include Text Only version for accessibility **/
	
 	UPDATE
	Versions
	SET
SectionContent=@SectionContent,
SectionContent_lang2=@SectionContent_lang2,
TextOnly=@TextOnly
	WHERE     Versions.SectionID =@SectionID and Versions.VersionId=@VersionId' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Account_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Account_Insert    Script Date: 10/5/2001 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Account_Insert]
	(
		@UserName NVARCHAR(32) = NULL,
		@Password NVARCHAR(40) = NULL,
		@Email NVARCHAR(64)    = NULL,
		@ModifiedDate DATETIME = NULL,
		@CreationDate DATETIME = NULL
	)
AS
	SET NOCOUNT ON
	
	INSERT INTO Account (UserName, Password, Email, ModifiedDate, CreationDate) 
	       VALUES (@UserName, @Password, @Email, @ModifiedDate, @CreationDate)
	RETURN 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Section_SetStatus]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Section_SetStatus    Script Date: 15/9/2003 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Section_SetStatus]
	(
		@SectionID INT         = NULL,
		@SectionVersion INT           = NULL,
		@Status INT            = NULL,
		@ModifiedDate DATETIME = NULL
	)
AS
	SET NOCOUNT ON
	
	UPDATE SectionElements
	SET 
		   Status       = @Status,
		   Datemodified=@ModifiedDate
	         
	       
	WHERE  SectionID = @SectionID
AND SectionVersion=@SectionVersion
	
	RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Account_Remove]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Account_Remove    Script Date: 10/5/2001 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Account_Remove]
	(
		@AccountID INT = NULL
	)
AS
    DELETE FROM Account 
    
    WHERE AccountID=@AccountID
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountRoles]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AccountRoles](
	[AccountID] [int] NOT NULL,
	[Role] [char](32) NOT NULL,
	[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Pages_GetSection]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Pages_GetSection   Script Date: 14/12/2003 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Pages_GetSection]
(

@SectionID Int,
@PageID Int,
@Status int
)

AS


SELECT  SectionContent
FROM         Versions
WHERE     (Versions.SectionID =@SectionID) AND (PageID = @PageID) 
AND Status=@Status
RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Content](
	[ContentID] [int] NOT NULL,
	[Version] [int] NOT NULL CONSTRAINT [DF_Content_Version]  DEFAULT ((1)),
	[SectionId] [int] NULL,
	[PageId] [int] NULL,
	[ProjectId] [int] NULL,
	[FriendlyName] [varchar](200) NULL CONSTRAINT [DF_Content_FriendlyName]  DEFAULT (''),
	[Protected] [int] NULL,
	[TypeID] [int] NULL,
	[Headline] [text] NULL CONSTRAINT [DF_Content_Headline]  DEFAULT (''),
	[Headline2] [text] NULL,
	[Url] [text] NULL,
	[ExternalLink] [int] NULL CONSTRAINT [DF_Content_ExternalLink]  DEFAULT ((0)),
	[Description] [varchar](20) NULL CONSTRAINT [DF_Content_Description]  DEFAULT (''),
	[Body] [text] NULL,
	[Body2] [text] NULL,
	[ImageId] [int] NULL,
	[MediaUrl] [varchar](500) NULL,
	[Status] [int] NOT NULL,
	[Editor] [int] NOT NULL CONSTRAINT [DF_Content_Editor]  DEFAULT ((0)),
	[Approver] [int] NOT NULL CONSTRAINT [DF_Content_Approver]  DEFAULT ((0)),
	[Writer] [int] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Content_ModifiedDate]  DEFAULT (getdate()),
	[CreationDate] [datetime] NOT NULL CONSTRAINT [DF_Content_CreationDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_Content] PRIMARY KEY CLUSTERED 
(
	[ContentID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Content]') AND name = N'IX_Content')
CREATE NONCLUSTERED INDEX [IX_Content] ON [dbo].[Content] 
(
	[ContentID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Content', @level2type=N'COLUMN', @level2name=N'Editor'

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Account_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Account_Update    Script Date: 10/5/2001 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Account_Update]
	(
		@AccountID INT         = NULL,
		@UserName NVARCHAR(32) = NULL,
		@Password NVARCHAR(40) = NULL,
		@Email NVARCHAR(64)    = NULL,
		@ModifiedDate DATETIME = NULL
	)
AS
	SET NOCOUNT ON
	
	UPDATE Account
	SET UserName = @UserName,
	    Password = @Password,
	    Email    = @Email,
	    ModifiedDate = @ModifiedDate
	WHERE AccountID = @AccountID
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Template_Elts_backup]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Template_Elts_backup](
	[TemplateId] [int] NULL,
	[TemplateEltId] [int] NOT NULL,
	[Type_T_Elt] [varchar](50) NOT NULL,
	[EltCode] [text] NULL,
	[EltOrder] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountProperty_GetValue]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.AccountProperty_GetValue    Script Date: 10/5/2001 2:36:39 AM ******/
CREATE PROCEDURE [dbo].[AccountProperty_GetValue]
	(
		@AccountID INT         = NULL,
		@Property NVARCHAR(32) = NULL
	)
AS
	SET NOCOUNT ON
    SELECT Value
      FROM AccountProperty
     WHERE (AccountID = @AccountID AND Property = @Property)
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountProperty]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AccountProperty](
	[AccountID] [int] NOT NULL,
	[Property] [varchar](50) NOT NULL,
	[Value] [varchar](200) NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
	[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountProperty_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.AccountProperty_Insert    Script Date: 10/5/2001 2:36:39 AM ******/
CREATE PROCEDURE [dbo].[AccountProperty_Insert]
	(
		@AccountID INT         = NULL,
		@Property NVARCHAR(32) = NULL,
		@Value TEXT            = NULL,
		@ModifiedDate DATETIME = NULL,
		@CreationDate DATETIME = NULL
	)
AS
	SET NOCOUNT ON
	
	INSERT INTO AccountProperty (AccountID, Property, Value, ModifiedDate, CreationDate) 
	       VALUES (@AccountID, @Property, @Value, @ModifiedDate, @CreationDate)
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Library_Folders]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Library_Folders](
	[FolderId] [int] IDENTITY(1,1) NOT NULL,
	[ProjectId] [int] NULL,
	[Parent] [int] NULL,
	[Name] [varchar](80) NOT NULL,
	[Description] [varchar](2000) NULL,
	[Name_Lang2] [varchar](100) NULL,
	[Description_Lang2] [varchar](2000) NULL,
	[Type] [int] NULL CONSTRAINT [DF_Library_Folders_Type]  DEFAULT (1)
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountProperty_Remove]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.AccountProperty_Remove    Script Date: 10/5/2001 2:36:39 AM ******/
CREATE PROCEDURE [dbo].[AccountProperty_Remove]
	(
		@AccountID INT = NULL
	)
AS
    DELETE FROM AccountProperty 
    
    WHERE AccountID=@AccountID
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Deployment]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Deployment](
	[DeployID] [int] NOT NULL,
	[PageId] [int] NOT NULL,
	[ServerId] [int] NOT NULL,
	[Created] [datetime] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountProperty_RemoveProperty]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.AccountProperty_RemoveProperty    Script Date: 10/5/2001 2:36:39 AM ******/
CREATE PROCEDURE [dbo].[AccountProperty_RemoveProperty]
	(
		@AccountID INT = NULL,
		@Property NVARCHAR(32) = NULL
	)
AS
    DELETE FROM AccountProperty 
    
    WHERE AccountID=@AccountID
      AND Property=@Property
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Pages]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Pages](
	[PageId] [int] NOT NULL,
	[PageName] [varchar](70) NULL,
	[PageURL] [varchar](1000) NULL,
	[PageActive] [bit] NOT NULL,
	[Project] [int] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountProperty_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.AccountProperty_Update    Script Date: 10/5/2001 2:36:39 AM ******/
CREATE PROCEDURE [dbo].[AccountProperty_Update]
	(
		@AccountID INT         = NULL,
		@Property NVARCHAR(32) = NULL,
		@Value TEXT            = NULL,
		@ModifiedDate DATETIME = NULL
	)
AS
	SET NOCOUNT ON
	
	UPDATE AccountProperty
	SET 
		Value = @Value,
	    ModifiedDate = @ModifiedDate
	    
	WHERE (AccountID = @AccountID AND Property = @Property)
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Roles]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Roles](
	[RoleID] [int] NOT NULL,
	[Role] [char](32) NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ContentNotes_CountNotesForID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.ContentNotes_CountNotesForID    Script Date: 10/5/2001 2:36:39 AM ******/
CREATE PROCEDURE [dbo].[ContentNotes_CountNotesForID]
	(
		@ContentID INT = NULL
	)
AS
	SET NOCOUNT ON
    SELECT COUNT(*) 
    FROM ContentNotes 
    
    WHERE ContentID=@ContentID
	
	RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ContentNotes_GetNote]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.ContentNotes_GetNote    Script Date: 10/5/2001 2:36:39 AM ******/
CREATE PROCEDURE [dbo].[ContentNotes_GetNote]
	(
		@NoteID INT = NULL
	)
AS
	SET NOCOUNT ON
	
      SELECT * 
        FROM ContentNotes 
    
       WHERE NoteID=@NoteID
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Icons]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Icons](
	[IconId] [int] IDENTITY(1,1) NOT NULL,
	[ProjectId] [int] NOT NULL CONSTRAINT [DF_Icons_ProjectId]  DEFAULT (1),
	[IconName] [varchar](100) NOT NULL,
	[Icon] [image] NOT NULL,
	[Width] [int] NOT NULL CONSTRAINT [DF_Icons_Width]  DEFAULT (10),
	[Height] [int] NOT NULL CONSTRAINT [DF_Icons_Height]  DEFAULT (10),
 CONSTRAINT [PK_Icons] PRIMARY KEY CLUSTERED 
(
	[IconId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Image_GetThumbnailbyId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE  [dbo].[Image_GetThumbnailbyId] 
(
@ImageId Int
)
AS
select Thumbnail
from Library
where ImageId=@ImageId' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Account_GetAccountID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Account_GetAccountID    Script Date: 10/5/2001 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Account_GetAccountID]
	(
		@UserName NVARCHAR(32) = NULL
	)
AS
	SET NOCOUNT ON
    SELECT AccountID 
      FROM Account 
     WHERE UserName=@UserName
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Localization]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Localization](
	[Table] [varchar](200) NOT NULL,
	[Fieldname] [varchar](300) NOT NULL,
	[Language0] [varchar](500) NOT NULL,
	[Language1] [varchar](500) NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Image_GetThumbnailsByName]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE  [dbo].[Image_GetThumbnailsByName] 
AS
SELECT   ImageId, ImageName, Thumbnail
from Images
order by ImageId' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Template_GetList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Template_GetList    Script Date: 16/12/2003  ******/
CREATE PROCEDURE [dbo].[Template_GetList]
(
@ProjectId int
)
AS
	SET NOCOUNT ON
	
     SELECT     TemplateId, TemplateName
FROM         Templates
WHERE Project=@ProjectId
ORDER BY TemplateName
	
	RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Account_GetAccountForID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Account_GetAccountForID    Script Date: 10/5/2001 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Account_GetAccountForID]
	(
		@AccountID INT = NULL
	)
AS
	SET NOCOUNT ON
    SELECT   AccountID, UserName, Email, Password 
      FROM   Account 
     WHERE   AccountID=@AccountID
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sections]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Sections](
	[SectionId] [int] NOT NULL,
	[SectionName] [varchar](80) NULL,
	[Elt_Validity_start] [datetime] NULL,
	[Elt_Validity_end] [datetime] NULL,
	[SectionActive] [bit] NOT NULL,
	[PageID] [int] NULL,
	[TemplateID] [int] NULL,
	[Status] [int] NULL,
	[Editor] [int] NULL,
	[Notes] [text] NULL,
	[DateCreated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ContentNotes_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.ContentNotes_Insert    Script Date: 4/11/2003 ******/
CREATE PROCEDURE [dbo].[ContentNotes_Insert]
	(
		@ContentID INT         = NULL,
		@Note TEXT             = NULL,
		@Author INT            = NULL
	)
AS
	SET NOCOUNT ON
	
	INSERT INTO ContentNotes (ContentID, Note, Author) 
	       VALUES (@ContentID, @Note, @Author)
	RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ContentNotes_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.ContentNotes_Delete    Script Date: 4/11/2003 ******/
CREATE PROCEDURE [dbo].[ContentNotes_Delete]
	(
		@ContentID INT = NULL
	)
AS
    DELETE FROM ContentNotes 
    
    WHERE contentID=@ContentID
	
	RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ContentNotes_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.ContentNotes_Update    Script Date: 10/5/2001 2:36:40 AM ******/
CREATE PROCEDURE [dbo].[ContentNotes_Update]
	(
		@NoteID INT            = NULL,
		@Note TEXT             = NULL
	)
AS
	SET NOCOUNT ON
	
	UPDATE ContentNotes
	SET 
		   Note         = @Note
	       
	WHERE  NoteID = @NoteID
	
	RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Types_params]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Types_params](
	[TypeId] [int] NOT NULL,
	[TypeValue] [varchar](50) NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Distribution_UpdateRank]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Distribution_UpdateRank    Script Date: 10/5/2001 2:36:39 AM ******/
CREATE PROCEDURE [dbo].[Distribution_UpdateRank]
	(
		@ContentID INT         = NULL,
		@Version INT           = NULL,
		@ZoneID INT            = NULL,
		@Ranking INT           = NULL,
		@ModifiedDate DATETIME = NULL
	)
AS
	SET NOCOUNT ON
	
	UPDATE Distribution
	SET 
		   Ranking      = @Ranking,
	       ModifiedDate = @ModifiedDate
	       
	WHERE  ContentID = @ContentID
	  AND  Version = @Version
	  AND  ZoneID = @ZoneID
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ClickLog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ClickLog](
	[ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ClickLog_ID]  DEFAULT (newid()),
	[Link] [varchar](1000) NOT NULL,
	[Text] [varchar](200) NOT NULL,
	[LogDateTime] [smalldatetime] NOT NULL CONSTRAINT [DF_ClickLog_LogDateTime]  DEFAULT (getdate()),
	[Referer] [varchar](1000) NOT NULL,
	[RemoteIp] [varchar](100) NOT NULL,
	[count] [bigint] NOT NULL CONSTRAINT [DF_ClickLog_count]  DEFAULT ((1)),
 CONSTRAINT [PK_ClickLog] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Domain_GetAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Domain_GetAll    Script Date: 10/5/2001 2:36:39 AM ******/
CREATE PROCEDURE [dbo].[Domain_GetAll]
AS
	SET NOCOUNT ON
	
	SELECT * FROM Domain
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Servers]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Servers](
	[Id] [int] NOT NULL,
	[Name] [varchar](100) NOT NULL,
	[ServerRole] [varchar](100) NOT NULL,
	[ServerUrl] [varchar](50) NOT NULL,
	[Application] [varchar](100) NULL,
	[Project] [int] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Domain_GetDomainForID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Domain_GetDomainForID    Script Date: 10/5/2001 2:36:39 AM ******/
CREATE PROCEDURE [dbo].[Domain_GetDomainForID]
	(
		@did INT = NULL
	)
AS
	SET NOCOUNT ON
	
	SELECT * 
	  FROM Domain
	 WHERE DomainID=@did
	
	RETURN 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SectionContent_UpdateApplication]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Sections_GetTemplateId   Script Date: 20/05/2004  2:36:38 AM ******/
CREATE PROCEDURE [dbo].[SectionContent_UpdateApplication]
(

@SectionID Int,
@ServerId Int,
@SectionContent text
)

AS
 SET NOCOUNT ON



	
 	UPDATE
	SectionContent
	SET
SectionContent=@SectionContent

	WHERE     SectionID =@SectionID and ServerId=@ServerId' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountRoles_GetAllRole]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.AccountRoles_GetAllRole    Script Date: 10/5/2001 2:36:40 AM ******/
CREATE PROCEDURE [dbo].[AccountRoles_GetAllRole]
	(
		@Role NVARCHAR(32) = NULL
	)
AS
	SET NOCOUNT ON
    SELECT AccountID
      FROM AccountRoles
     WHERE Role=@Role
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountRoles_GetRolesForID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.AccountRoles_GetRolesForID    Script Date: 10/5/2001 2:36:40 AM ******/
CREATE PROCEDURE [dbo].[AccountRoles_GetRolesForID]
	(
		@AccountID INT = NULL
	)
AS
	SET NOCOUNT ON
    SELECT role
      FROM AccountRoles
     WHERE AccountID=@AccountID
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountRoles_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.AccountRoles_Insert    Script Date: 10/5/2001 2:36:40 AM ******/
CREATE PROCEDURE [dbo].[AccountRoles_Insert]
	(
		@AccountID INT         = NULL,
		@Role NVARCHAR(32)     = NULL,
		@CreationDate DATETIME = NULL
	)
AS
	SET NOCOUNT ON
    INSERT 
    INTO   AccountRoles ( AccountID,  Role,  CreationDate) 
    VALUES              (@AccountID, @Role, @CreationDate)
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountRoles_Remove]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.AccountRoles_Remove    Script Date: 10/5/2001 2:36:40 AM ******/
CREATE PROCEDURE [dbo].[AccountRoles_Remove]
	(
		@AccountID INT = NULL
	)
AS
    DELETE FROM AccountRoles
    
    WHERE AccountID=@AccountID
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ContentRank_GetRankID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.ContentRank_GetRankID    Script Date: 10/5/2001 2:36:40 AM ******/
CREATE PROCEDURE [dbo].[ContentRank_GetRankID]
	(
		@rank NVARCHAR(10) = NULL
	)
AS
	SET NOCOUNT ON
	
    SELECT RankID
      FROM ContentRank
     WHERE Rank=@rank   
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Library]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Library](
	[ImageId] [int] NOT NULL,
	[FolderId] [int] NOT NULL CONSTRAINT [DF_Library_FolderId]  DEFAULT (0),
	[ProjectId] [int] NOT NULL,
	[Language] [int] NULL CONSTRAINT [DF_Library_Language]  DEFAULT (0),
	[ImageData] [image] NULL,
	[ImageName] [varchar](200) NOT NULL,
	[MediaUrl] [varchar](500) NULL,
	[Width] [int] NOT NULL,
	[Height] [int] NOT NULL,
	[AltText] [varchar](200) NULL,
	[ContentType] [varchar](50) NOT NULL,
	[Thumbnail] [image] NULL,
	[Editor] [int] NULL,
	[MediaType] [int] NULL,
	[Credits] [varchar](500) NULL,
	[ShortDescription] [varchar](500) NULL,
	[Description] [text] NULL,
	[BroadcastDate] [smalldatetime] NULL,
	[Duration] [varchar](50) NULL,
	[Relevance] [varchar](1000) NULL,
	[CreationDate] [datetime] NULL CONSTRAINT [DF_Library_CreationDate]  DEFAULT (getdate()),
	[VideoLanguage] [varchar](100) NULL,
	[Compression] [int] NULL CONSTRAINT [DF_Library_Compression]  DEFAULT ((255))
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ContentRank_GetRanks]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.ContentRank_GetRanks    Script Date: 10/5/2001 2:36:40 AM ******/
CREATE PROCEDURE [dbo].[ContentRank_GetRanks]
AS
	SET NOCOUNT ON
	
    Select *
        FROM ContentRank 
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Distribution_GetOrdered]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Distribution_GetOrdered    Script Date: 10/5/2001 2:36:39 AM ******/
CREATE PROCEDURE [dbo].[Distribution_GetOrdered]
	(
		@ZoneID INT = NULL
	)
AS
	SET NOCOUNT ON
	
	  SELECT *
	    FROM Distribution
	   WHERE ZoneID=@ZoneID
	ORDER BY Ranking DESC, ModifiedDate DESC, ContentID DESC
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Distribution_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Distribution_Insert    Script Date: 10/5/2001 2:36:39 AM ******/
CREATE PROCEDURE [dbo].[Distribution_Insert]
	(
		@ContentID INT         = NULL,
		@Version INT           = NULL,
		@ZoneID INT            = NULL,
		@Ranking INT           = NULL,
		@ModifiedDate DATETIME = NULL,
		@CreationDate DATETIME = NULL
	)
AS
	SET NOCOUNT ON
	
	INSERT INTO Distribution (ContentID, Version, ZoneID, Ranking, 
	                          ModifiedDate, CreationDate) 
	       VALUES (@ContentID, @Version, @ZoneID, @Ranking, 
	               @ModifiedDate, @CreationDate)
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Distribution_Remove]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Distribution_Remove    Script Date: 10/5/2001 2:36:39 AM ******/
CREATE PROCEDURE [dbo].[Distribution_Remove]
	(
		@cid INT         = NULL,
		@ver INT         = NULL
	)
AS
    DELETE FROM Distribution 
    
    WHERE ContentID=@cid
      AND Version=@ver
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Images]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Images](
	[ImageId] [int] IDENTITY(1,1) NOT NULL,
	[ProjectId] [int] NOT NULL,
	[ImageData] [image] NULL,
	[ImageName] [varchar](200) NOT NULL,
	[Width] [int] NOT NULL CONSTRAINT [DF_Images_Width]  DEFAULT (1),
	[Height] [int] NOT NULL CONSTRAINT [DF_Images_Height]  DEFAULT (1),
	[AltText] [varchar](200) NULL,
	[ContentType] [varchar](50) NOT NULL,
	[Thumbnail] [image] NULL,
	[Editor] [int] NULL,
 CONSTRAINT [PK_Images] PRIMARY KEY CLUSTERED 
(
	[ImageId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_GetContentForIDVer]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetContentForIDVer    Script Date: 10/5/2001 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Content_GetContentForIDVer]
	(
		@cid INT = NULL,
		@ver INT = NULL
	)
AS
	SET NOCOUNT ON
	
    SELECT * 
    FROM Content 
    
    WHERE ContentID=@cid
      AND Version=@ver
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_GetHeadlines]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetHeadlines    Script Date: 16/12/2003 3:36:38 AM ******/
CREATE PROCEDURE [dbo].[Content_GetHeadlines]
(
@ProjectId int
)

AS
	SET NOCOUNT ON
	
SELECT     Content.ContentID, Content.Version, Content.Headline, Content.Status, Content.Approver, Types_params.TypeValue, Account.UserName, 
                      Content.ModifiedDate, Content.CreationDate, Content.Url, Content.Body, PageId, SectionId
FROM         Content INNER JOIN
                      Types_params ON Content.TypeID = Types_params.TypeId INNER JOIN
                      Account ON Content.Writer = Account.AccountID
WHERE Content.ProjectId=@ProjectId

	order by ContentID DESC
	RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_GetHeadlinesForApprove]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetHeadlinesForApprove    Script Date: 10/5/2001 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Content_GetHeadlinesForApprove]
	(
		@Approver INT = NULL
	)
AS
	SET NOCOUNT ON
	
      SELECT ContentID, Version, Headline, Status, Approver 
        FROM Content 
       WHERE Approver=@Approver
          OR Approver=0
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SectionContent]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SectionContent](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[SectionId] [int] NULL,
	[ServerId] [int] NULL,
	[SectionContent] [text] NULL,
	[SectionContent_Lang2] [text] NULL,
	[TextOnly] [text] NULL,
	[Modified] [datetime] NULL,
 CONSTRAINT [PK_SectionContent] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_GetHeadlinesForEdit]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetHeadlinesForEdit    Script Date: 10/5/2001 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Content_GetHeadlinesForEdit]
	(
		@Editor INT            = NULL
	)
AS
	SET NOCOUNT ON
	
      SELECT ContentID, Version, Headline, Status, Editor 
        FROM Content 
       WHERE Editor=@Editor
          OR Editor=0
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SectionElements]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SectionElements](
	[SectionElementID] [int] NOT NULL,
	[SectionElementType] [varchar](50) NULL,
	[SectionID] [int] NULL,
	[SectionVersion] [int] NULL,
	[ContentId] [int] NULL,
	[Status] [int] NULL,
	[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_SectionElements_DateCreated]  DEFAULT (getdate()),
	[DateModified] [datetime] NULL,
	[Author] [int] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ContentNotes_GetNotesForID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.ContentNotes_GetNotesForID    Script Date: 10/5/2001 2:36:39 AM ******/
CREATE PROCEDURE [dbo].[ContentNotes_GetNotesForID]
	(
		@ContentID INT = NULL
	)
AS
	SET NOCOUNT ON
	
      SELECT * 
        FROM ContentNotes 
    
       WHERE ContentID=@ContentID
	
	RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_NextContentID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_NextContentID    Script Date: 10/5/2001 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Content_NextContentID]
AS
	SET NOCOUNT ON
    SELECT DISTINCT ContentID 
    FROM     Content
    ORDER BY ContentID DESC
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_Remove]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_Remove    Script Date: 10/5/2001 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Content_Remove]
	(
		@cid INT         = NULL,
		@ver INT         = NULL
	)
AS
    DELETE FROM Content 
    
    WHERE ContentID=@cid
      AND Version=@ver
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_SetApproval]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_SetApproval    Script Date: 10/5/2001 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Content_SetApproval]
	(
		@ContentID INT         = NULL,
		@Version INT           = NULL,
		@Approver INT          = NULL,
		@Status INT            = NULL,
		@ModifiedDate DATETIME = NULL
	)
AS
	SET NOCOUNT ON
	
	UPDATE Content
	SET 
		   Status       = @Status,
		   Approver     = @Approver,
	       ModifiedDate = @ModifiedDate
	       
	WHERE  ContentID = @ContentID
	  AND  Version = @Version
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_SetEditor]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_SetEditor    Script Date: 10/5/2001 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Content_SetEditor]
	(
		@ContentID INT         = NULL,
		@Version INT           = NULL,
		@Editor INT            = NULL,
		@Status INT            = NULL,
		@ModifiedDate DATETIME = NULL
	)
AS
	SET NOCOUNT ON
	
	UPDATE Content
	SET 
		   Status       = @Status,
		   Editor       = @Editor,
	       ModifiedDate = @ModifiedDate
	       
	WHERE  ContentID = @ContentID
	  AND  Version = @Version
	  AND  Editor = 0
	
	RETURN 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_UpdateForEditing]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_Updateforediting    Script Date: 18/05/2006 ******/
CREATE PROCEDURE [dbo].[Content_UpdateForEditing]
	(
		@ContentID INT         = NULL,
		@Version INT           = NULL,
		@Protected INT         = NULL,
		@Page INT            = NULL,
		@Section INT          = NULL,
		@Headline TEXT         = NULL,
		@Url TEXT         = NULL,
		@ExternalLink INT,
		@Body TEXT             = NULL,
		@Status INT            = NULL,
		@Editor INT            = NULL,
		@Approver INT          = NULL,
		@Writer INT      = NULL,
		@ModifiedDate DATETIME = NULL,
		@FriendlyName varchar(200)
	)
AS
	SET NOCOUNT ON
	UPDATE Content
	SET 
		   Protected    =0,
		   Headline     = @Headline,
		  Url=@Url,
		  ExternalLink = @ExternalLink,
		 PageId=@Page,
		 SectionId=@Section,
		FriendlyName=@FriendlyName,
		   Body         = @Body,
		   Status       = @Status,
		   Editor       = @Editor,
		   Approver     = @Approver,
	      	 ModifiedDate = @ModifiedDate

/*
   Writer = @Writer,
*/
	       
	WHERE  ContentID = @ContentID
	  AND  Version = @Version


	RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Template_Elts]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Template_Elts](
	[TemplateId] [int] NULL,
	[TemplateEltId] [int] NOT NULL,
	[Type_T_Elt] [varchar](50) NOT NULL,
	[EltCode] [text] NULL,
	[EltOrder] [int] NULL CONSTRAINT [DF_Template_Elts_EltOrder]  DEFAULT (0)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_SetProtected]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_SetProtected    Script Date: 10/5/2001 2:36:40 AM ******/
CREATE PROCEDURE [dbo].[Content_SetProtected]
	(
		@ContentID INT         = NULL,
		@Version INT           = NULL,
		@Protected INT         = NULL,
		@ModifiedDate DATETIME = NULL
	)
AS
	SET NOCOUNT ON
	
	UPDATE Content
	SET 
		   Protected    = @Protected,
	       ModifiedDate = @ModifiedDate
	       
	WHERE  ContentID = @ContentID
	  AND  Version = @Version
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_SetStatus]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_SetStatus    Script Date: 10/5/2001 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Content_SetStatus]
	(
		@ContentID INT         = NULL,
		@Version INT           = NULL,
		@Status INT            = NULL,
		@ModifiedDate DATETIME = NULL
	)
AS
	SET NOCOUNT ON
	
	UPDATE Content
	SET 
		   Status       = @Status,
	       ModifiedDate = @ModifiedDate
	       
	WHERE  ContentID = @ContentID
	  AND  Version = @Version
	
	RETURN 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Archives]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Archives](
	[ArchiveId] [int] NOT NULL,
	[Url] [text] NULL,
	[Description] [varchar](20) NULL,
	[Body] [text] NULL,
	[Image] [image] NULL,
	[PageUsed] [int] NULL,
	[SectionUsed] [int] NULL,
	[ProjectUsed] [int] NULL,
	[Created] [datetime] NULL,
	[Modified] [datetime] NULL,
	[Archived] [datetime] NULL,
 CONSTRAINT [PK_Archives] PRIMARY KEY CLUSTERED 
(
	[ArchiveId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_Update    Script Date: 18/05/2006 ******/
CREATE PROCEDURE [dbo].[Content_Update]
	(
		@ContentID INT         = NULL,
		@Version INT           = NULL,
		@Protected INT         = NULL,
		@Page INT            = NULL,
		@Section INT          = NULL,
		@Headline TEXT         = NULL,
		@Url TEXT         = NULL,
		@ExternalLink INT,
		@Body TEXT             = NULL,
		@Status INT            = NULL,
		@Editor INT            = NULL,
		@Approver INT          = NULL,
		@Writer INT      = NULL,
		@ModifiedDate DATETIME = NULL,
		@FriendlyName varchar(200),
		@Body2 TEXT,
		@Headline2 as TEXT
	)
AS
	SET NOCOUNT ON
	UPDATE Content
	SET 
		   Protected    =0,
		   Headline     = @Headline,
		  Headline2=@Headline2,
		  Url=@Url,
		  ExternalLink = @ExternalLink,
		 PageId=@Page,
		 SectionId=@Section,
		FriendlyName=@FriendlyName,
		   Body         = @Body,
		 Body2=@Body2,
		   Status       = @Status,		
		   Approver     = @Approver,
	      	 ModifiedDate = @ModifiedDate
/*
		   Writer = @Writer,
	          Editor       = @Editor,
*/

	WHERE  ContentID = @ContentID
	  AND  Version = @Version

/*  IF @Body2 is not null 
UPDATE Content
	SET 
		  Headline2=@Headline2,
		 Body2=@Body2
	       
	WHERE  ContentID = @ContentID
	  AND  Version = @Version

	RETURN 
*/' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Pages_GetDeploymentList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Pages_GetDeploymentList    Script Date: 30/10/2003 ******/
CREATE PROCEDURE [dbo].[Pages_GetDeploymentList]
(

@Filter Int,
@Status Int,
@ProjectId int
)
AS

IF @Filter=0
	
SELECT DISTINCT Pages.PageName, Pages.PageId
FROM         Pages INNER JOIN
                      Versions ON Pages.PageId = Versions.PageId
WHERE     (Versions.Status = @Status) AND Pages.Project=@ProjectId

ELSE

SELECT DISTINCT Pages.PageName, Pages.PageId
FROM         Sections INNER JOIN
                      Pages ON Sections.PageID = Pages.PageId AND Pages.Project=@ProjectId

	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure  Content_Delete    Script Date: 14/06/2004 ******/
CREATE PROCEDURE [dbo].[Content_Delete]
	(
		@ContentID INT = NULL
	)
AS
    DELETE FROM Content
    
    WHERE ContentId=@ContentID
	
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_GetByStatus]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetHeadlinesForAuthor    Script Date:15/06/2004 ******/
CREATE PROCEDURE [dbo].[Content_GetByStatus]
	(
		@Status INT            = NULL,
		@ProjectID INT = NULL,
	@Startdate Datetime,
@EndDate Datetime
	)
AS
	SET NOCOUNT ON
	
 SELECT     Content.ContentID, Content.Headline, Content.FriendlyName,Content.Url,Content.PageID, Content.SectionID,Content.ProjectID, Content.ModifiedDate,
 Content.CreationDate, Account.UserName, Content.Status
FROM         Content INNER JOIN
                      Account ON Content.Writer = Account.AccountID
WHERE     Content.Status =@Status AND ProjectId=@ProjectId
AND (Content.ModifiedDate BETWEEN @StartDate AND @EndDate)
	
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sections_GetNotes]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Sections_GetNotes   Script Date: 12/10/2003 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Sections_GetNotes]
(

@SectionID Int,
@PageID Int
)

AS
	SET NOCOUNT ON
	
 SELECT    Notes
FROM         Sections
WHERE     (Sections.SectionID =@SectionID) AND (Sections.PageID = @PageID)
	RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Image_Insert2]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Image_Insert2]
	-- Add the parameters for the stored procedure here

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

 
END
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Versions_GetLastSectionId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Versions_GetLastSectionId   Script Date: 17:17 10/09/2003 ******/
CREATE PROCEDURE [dbo].[Versions_GetLastSectionId]
	(
		@SectionID    INT = NULL
	)
AS
	
DECLARE @LastID   int


SELECT TOP 1 @LastID =   VersionId
FROM         Versions
WHERE     (SectionID = @SectionID)
GROUP BY VersionId
ORDER BY VersionId DESC

if @LastId is  null

SET @LastID=0

RETURN @LastId+1' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ContentNotes]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ContentNotes](
	[NoteID] [int] IDENTITY(1,1) NOT NULL,
	[ContentID] [int] NOT NULL,
	[Note] [text] NOT NULL,
	[Author] [int] NOT NULL,
	[CreationDate] [datetime] NOT NULL CONSTRAINT [DF_ContentNotes_CreationDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_ContentNotes] PRIMARY KEY CLUSTERED 
(
	[NoteID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Versions_Create]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Versions_Create   Script Date: 26/02/2004 ******/
CREATE PROCEDURE [dbo].[Versions_Create]
	(
		@SectionID    INT = NULL,
		@PageID    INT = NULL,
		@Editor INT=NULL
	)
AS
	
DECLARE @LastID   int


SELECT TOP 1 @LastID =   VersionId
FROM         Versions
WHERE     (SectionID = @SectionID)
GROUP BY VersionId
ORDER BY VersionId DESC

if @LastId is  null

SET @LastID=0

/** Insert a new version**/

Insert into versions
( VersionId, SectionId, PageId,Status, Editor)
VALUES
(@LastId+1, @SectionId, @PageId,4, @Editor)



RETURN @LastId+1' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Versions_SetStatus]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Versions_SetStatus    Script Date: 12/11/2003 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Versions_SetStatus]
	(
		@SectionID INT         = NULL,
		@VersionId INT           = NULL,
		@Status INT            = NULL,
		@Editor INT            = NULL,
		@Modified DATETIME = NULL
	)
AS
	SET NOCOUNT ON
	
	UPDATE Versions
	SET 
		   Status       = @Status,
		   Editor=@Editor,
		   Modified=@Modified
	         
	       
	WHERE  SectionID = @SectionID
AND VersionId=@VersionId
	
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Settings_GetListServers]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Settings_GetListServers]  
(
@ProjectId INT

)


AS
SELECT     Servers.Id, Servers.Name
FROM         Servers INNER JOIN
                      Projects ON Servers.Project = Projects.ProjectId
WHERE     (Servers.Project = @ProjectId)
ORDER BY Servers.Id' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Settings_GetListProjects]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Settings_GetListProjects]  AS
SELECT  ProjectId,Name FROM Projects order by ProjectId' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Image_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure  Content_Delete    Script Date: 3/07/2004 ******/
CREATE PROCEDURE [dbo].[Image_Delete]
	(
		@ImageID INT = NULL
	)
AS
    DELETE FROM Library
    
    WHERE ImageId=@ImageID
	
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Pages_DeployForPreview]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Pages_DeployForPreview]
(
@PageId int
)

AS

/** Insert in a temporary table to preview a page**/

Insert INTO #Preview
SELECT     Versions.SectionContent, (getdate())
FROM         Versions INNER JOIN
                      Sections ON Versions.SectionId = Sections.SectionId 
WHERE     (Versions.PageId = @PageId)' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Templates]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Templates](
	[TemplateId] [int] IDENTITY(1,1) NOT NULL,
	[Project] [int] NULL,
	[TemplateName] [varchar](50) NULL,
	[TemplateCreated] [datetime] NOT NULL CONSTRAINT [DF_Templates_TemplateCreated]  DEFAULT (getdate()),
	[TemplateCode] [text] NULL,
 CONSTRAINT [PK_Templates] PRIMARY KEY CLUSTERED 
(
	[TemplateId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Section_Approve]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Section_Approve    Script Date: 10/10/2003 2:36:40 AM ******/
CREATE PROCEDURE [dbo].[Section_Approve]
	(
		@SectionID INT         = NULL,
		@PageID INT         = NULL,
		@Status INT            = NULL
	)
AS
	SET NOCOUNT ON

	UPDATE Versions
	SET 
		  
		   Status       = @Status
		 
	       
	WHERE  SectionID = @SectionID
          AND PageId= @PageId
	AND Status=8
	RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Mail]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Mail](
	[MessageId] [int] IDENTITY(1,1) NOT NULL,
	[Project] [int] NOT NULL,
	[Name] [varchar](200) NOT NULL,
	[Email] [varchar](300) NOT NULL,
	[Tel] [varchar](100) NULL,
	[Fax] [varchar](100) NULL,
	[Address] [text] NULL,
	[Created] [datetime] NOT NULL CONSTRAINT [DF_Mail_Created]  DEFAULT (getdate()),
 CONSTRAINT [PK_Mail] PRIMARY KEY CLUSTERED 
(
	[MessageId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Settings_GetProjectById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Settings_GetProjectById] 
(
@ProjectId Int
)
 AS
SELECT  ProjectId,Name FROM Projects
WHERE ProjectId=@ProjectId' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Account]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Account](
	[AccountID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [varchar](50) NOT NULL,
	[Password] [varchar](50) NOT NULL,
	[Email] [varchar](200) NULL,
	[ModifiedDate] [datetime] NOT NULL,
	[CreationDate] [datetime] NOT NULL CONSTRAINT [DF_Account_CreationDate]  DEFAULT (getdate()),
	[ClearPwd] [varchar](50) NULL,
 CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED 
(
	[AccountID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Version_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Version_Delete  Script Date: 11/08/2004 ******/
CREATE PROCEDURE [dbo].[Version_Delete]
(

@SectionID Int,
@SectionVersionID Int
)

AS
 SET NOCOUNT ON

/** Check if the Element already exist **/
if exists(

select Versions.VersionID
From Versions
WHERE     (Versions.SectionID =@SectionID) 
AND (Versions.VersionID= @SectionVersionID)
)
	
 	DELETE
	Versions
WHERE     (Versions.SectionID =@SectionID) 
AND (Versions.VersionID= @SectionVersionID)' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Search]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Search]
@Keyword varchar(8000),
@LimitRes int,
@ProjectId int
AS
Set ROWCOUNT @LimitRes

SELECT     KEY_TBL.*, Pages.PageId, Pages.PageName, Pages.PageURL, FT_TBL.TextOnly, FT_TBL.SectionContent
FROM         Projects INNER JOIN
                      Pages ON Projects.ProjectId = Pages.Project INNER JOIN
                      Versions FT_TBL INNER JOIN
                      CONTAINSTABLE(dscribe_live.dbo.Versions, *, @Keyword) KEY_TBL ON FT_TBL.Id = KEY_TBL.[KEY] 
ON Pages.PageId = FT_TBL.PageId
WHERE     (FT_TBL.Status = 64) AND (Projects.ProjectId = @ProjectId)


ORDER BY KEY_TBL.RANK DESC

SET ROWCOUNT 0' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Versions]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Versions](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[VersionId] [int] NOT NULL,
	[SectionId] [int] NOT NULL,
	[PageId] [int] NULL,
	[SectionContent] [text] NULL,
	[SectionContent_Lang2] [text] NULL,
	[TextOnly] [text] NULL,
	[Status] [bigint] NULL,
	[Editor] [int] NULL,
	[Created] [datetime] NOT NULL CONSTRAINT [DF_Versions_Created]  DEFAULT (getdate()),
	[Modified] [datetime] NULL,
 CONSTRAINT [PK_Versions] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Template_Elts2]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Template_Elts2](
	[TemplateId] [int] NULL,
	[TemplateEltId] [int] NOT NULL,
	[Type_T_Elt] [varchar](50) NOT NULL,
	[EltCode] [text] NULL,
	[EltOrder] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Article_GetByID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Article_GetByID]
(
		 @contentid INT  
	)
AS
SELECT    *
FROM      Content 
    
    WHERE ContentID=@ContentId' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Search_ByArticles]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Search_ByArticles]
(
@Keyword varchar(1000),
@ProjectId int,
@CurrentPage int,
@PageSize int,
@TotalRecords int output
)
AS
CREATE TABLE #TempTable
(
  ID int IDENTITY PRIMARY KEY,
 ContentId  integer
)
--Fill the temp table with the Images  data
-- By Title
INSERT INTO #TempTable 
(
  ContentId
)
(
SELECT DISTINCT  ContentId
FROM        projects INNER JOIN
                   .Content FT_TBL INNER JOIN
                      CONTAINSTABLE(Content, *, @Keyword) KEY_TBL ON FT_TBL.ContentID = KEY_TBL.[KEY] ON 
                      projects.ProjectId = FT_TBL.ProjectId
WHERE     (FT_TBL.Status = 32) AND (FT_TBL.TypeID = 3)  AND ( FT_TBL.ProjectId=@ProjectId)
)
--Create variable to identify the first and last record that should be selected
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
--Select one page of data based on the record numbers above
SELECT 
    ContentId
FROM 
  #TempTable
WHERE 
  ID > @FirstRec 
AND
  ID < @LastRec
--Return the total number of records available as an output parameter
SELECT @TotalRecords = Count(Distinct ContentId) FROM  #TempTable' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Search_ByRank]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Search_ByRank]
@Keyword varchar(8000),
@LimitRes int,
@ProjectId int
AS
Set ROWCOUNT @LimitRes
(
SELECT DISTINCT    ''Article'' AS col1, KEY_TBL.*, dscribe.dbo.projects.Name AS ProjectName
FROM         dscribe.dbo.projects INNER JOIN
                      dscribe.dbo.Content FT_TBL INNER JOIN
                      CONTAINSTABLE(dscribe.dbo.Content, *, @Keyword) KEY_TBL ON FT_TBL.ContentID = KEY_TBL.[KEY] ON 
                      dscribe.dbo.projects.ProjectId = FT_TBL.ProjectId
WHERE     (FT_TBL.Status = 32) AND (FT_TBL.TypeID = 3)  AND ( FT_TBL.ProjectId=@ProjectId)
)
UNION ALL

(

SELECT   DISTINCT  ''Content'' AS col1, KEY_TBL.*, dscribe.dbo.projects.Name  AS ProjectName
FROM         dscribe.dbo.projects INNER JOIN
                      dscribe.dbo.Content FT_TBL INNER JOIN
                      CONTAINSTABLE(dscribe.dbo.Content, *, @Keyword) KEY_TBL ON FT_TBL.ContentID = KEY_TBL.[KEY] ON 
                      dscribe.dbo.projects.ProjectId = FT_TBL.ProjectId
WHERE     (FT_TBL.Status = 32) AND (FT_TBL.TypeID = 4) AND (FT_TBL.ProjectId = @ProjectId)
)



ORDER BY KEY_TBL.RANK DESC

SET ROWCOUNT 0' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Settings_GetProjectUrl]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Settings_GetProjectUrl]  
(
@ProjectId INT
)
AS
SELECT     Projects.Url
FROM         Projects 
WHERE     (Projects.ProjectId = @ProjectId)
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_GetContentByPage]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetContentbypage    Script Date: 15/09/2005 ******/
CREATE PROCEDURE [dbo].[Content_GetContentByPage]
	(
		@DsPage varchar(80),
		@DsSection varchar(70),
		@Domain varchar(80),
		@Project varchar(80)
	)
AS
	SET NOCOUNT ON
IF @DsPage <> ''Article'' 
	
SELECT     SectionContent.SectionId, SectionContent.SectionContent,  SectionContent.SectionContent_lang2 as SectionContent2,SectionContent.TextOnly
FROM         Servers INNER JOIN
                      SectionContent INNER JOIN
                      Sections INNER JOIN
                      Pages INNER JOIN
                      Projects ON Pages.Project = Projects.ProjectId ON Sections.PageID = Pages.PageId ON SectionContent.SectionId = Sections.SectionId ON 
                      Servers.Id = SectionContent.ServerId
	WHERE     (Pages.PageName = @DsPage) AND (Sections.SectionName = @DsSection) AND (Pages.PageActive = 1) AND (Servers.ServerUrl = @Domain)
	AND (Projects.Name=@Project)
ELSE
	
SELECT     Content.Body AS Sectioncontent,Content.Body2 SectionContent2,Content.ContentID, Content.SectionId
	FROM         Pages INNER JOIN
                      Content ON Pages.PageId = Content.PageId INNER JOIN
                      Servers ON Pages.Project = Servers.Project INNER JOIN
                      Projects ON Servers.Project = Projects.ProjectId
	WHERE     ( CAST(Content.ContentId AS varchar(80))=@DsSection) AND (Pages.PageActive = 1) AND (Servers.ServerUrl = @Domain)
	AND (Projects.Name=@Project)
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MediaType_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure  MediaType_Insert   Script Date:15/08/2005 ******/
CREATE PROCEDURE [dbo].[MediaType_Insert] ( 
@ProjectID int, 
@Name varchar(50),
@icon [image]
)
AS 
begin transaction 
  INSERT INTO Library_Mediatypes ( 
ProjectId,
Icon,
[Name]
 )
VALUES ( 
  @ProjectID, 
  @icon, 
  @Name
)
commit transaction' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Icon_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure  Icon_Insert   Script Date:9/10/2004 ******/
CREATE PROCEDURE [dbo].[Icon_Insert] ( 
@ProjectID int, 
@Name varchar(50),
@icon [image], 
@imgWidth int,
@imgHeight int
)
AS 
begin transaction 
  INSERT INTO Icons ( 
ProjectId,
Icon,
IconName,
Width,
Height
 )
VALUES ( 
  @ProjectID, 
  @icon, 
  @Name, 
@imgWidth,
@imgHeight
)
commit transaction' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Icon_GetbyId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE  [dbo].[Icon_GetbyId] 
(
@IconId Int
)
AS
select Icon
from Icons
where IconId=@IconId' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_Insert    Script Date: 18/09/2005 ******/
CREATE PROCEDURE [dbo].[Content_Insert]
	(
		@ContentID INT,
		@ProjectID INT         = NULL,
		@Version INT           = NULL,
		@Protected INT         = NULL,
		@Page INT            = NULL,
		@Section INT          = NULL,
		@TypeValue varchar(60)        = NULL,
		@Headline TEXT         = NULL,
		@Url TEXT         = NULL,
		@Body TEXT             = NULL,
		@Status INT            = NULL,
		@Editor INT            = NULL,
		@Approver INT       ,
		@Writer INT      = NULL,
		@ModifiedDate DATETIME = NULL,
		@CreationDate DATETIME = NULL,
		@FriendlyName varchar(200),
		@MediaUrl varchar(300),
		@ExternalLink INT,
		@Body2 TEXT,
		@Headline2 as TEXT
	)
AS
	SET NOCOUNT ON
Declare @TypeID int
SET @TypeID = (Select TypeID from Types_Params where TypeValue=@Typevalue)
	
	INSERT INTO Content (ContentID, ProjectId, Version, Protected, PageID, SectionID,  Headline, 
	                     Headline2,Body,Body2, Url,  Status, Editor, Approver, Writer, TypeID,
	                     ModifiedDate, CreationDate, MediaUrl, FriendlyName, ExternalLink) 
	       VALUES (@ContentID, @ProjectId,  @Version, 0, @Page, @Section, @Headline, 
	               @Headline2,@Body,@Body2, @Url,  @Status, @Editor, @Approver, @Writer,  @TypeID,
	               @ModifiedDate, @CreationDate, @MediaUrl, @FriendlyName, @ExternalLink)

	RETURN
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sections_GetSectionForHTML]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Sections_GetSectionForHTML   Script Date: 15/09/2005 ******/
CREATE PROCEDURE [dbo].[Sections_GetSectionForHTML]
(
@SectionID Int,
@ElementID Int,
@ElementType varchar(50),
@SectionVersion int
)
AS
	SET NOCOUNT ON
	
 SELECT     SectionElements.SectionElementID, Content.ContentID, Content.Version, Content.Headline, Content.Headline2,Content.Status, Content.Approver, 
                      Types_params.TypeValue, Content.Body, Content.Body2,Content.Url, Content.ImageId, Content.MediaUrl, Content.ExternalLink
FROM         Content INNER JOIN
                      Types_params ON Content.TypeID = Types_params.TypeId INNER JOIN
                      SectionElements ON Content.ContentID = SectionElements.ContentId
WHERE     (SectionElements.SectionID =@SectionID) AND (SectionElements.SectionelementID = @elementID)
AND (SectionElements.Sectionelementtype = @elementtype)
 AND (SectionElements.Sectionversion = @SectionVersion)
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sections_GetListByPage]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Sections_GetListByPage    Script Date: 1/10/2004 ******/
CREATE PROCEDURE [dbo].[Sections_GetListByPage]
(
@PageID Int 
)
AS
	SET NOCOUNT ON
	
     SELECT     Sections.SectionId, Sections.SectionName, Sections.TemplateId,Sections.Datecreated
FROM         Sections INNER JOIN
                      Pages ON Sections.PageID = Pages.PageId
WHERE    Pages.PageId = @PageID
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sections_GetList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetList    Script Date: 1/10/2004 ******/
CREATE PROCEDURE [dbo].[Sections_GetList]
(
@PageID Int
)
AS
	SET NOCOUNT ON
	
   SELECT     Sections.SectionId, Sections.SectionName, Sections.DateCreated, Templates.TemplateName
FROM         Sections INNER JOIN
                      Pages ON Sections.PageID = Pages.PageId INNER JOIN
                      Templates ON Sections.TemplateID = Templates.TemplateId
WHERE    Pages.PageId = @PageID
order by SectionName
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_GetByID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Content_GetByID]
	(
		 @contentid INT  
	)
AS
SELECT     *
FROM         Content INNER JOIN
                      Pages ON Content.PageId = Pages.PageId INNER JOIN
                      Sections ON Content.SectionId = Sections.SectionId
    
    WHERE ContentID=@ContentId' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Pages_GetByStatus]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Pages_GetByStatus    Script Date: 5/01/2007 ******/
CREATE PROCEDURE [dbo].[Pages_GetByStatus]
(
@ProjectId int,
@Status Int

)
AS
	SET NOCOUNT ON
	
    SELECT DISTINCT Pages.PageName, Sections.SectionName, Account.UserName, Sections.DateCreated, CONVERT(varchar, Pages.PageId) + ''@'' + CONVERT(varchar, 
                      Sections.SectionId) AS Aid
FROM           Sections INNER JOIN
                      Pages ON Sections.PageID = Pages.PageId INNER JOIN
                      Account ON Sections.Editor = Account.AccountID INNER JOIN
                      Versions ON Sections.SectionId = Versions.SectionId
WHERE     (Versions.Status = @Status) and (Pages.Project=@ProjectId)
ORDER BY Pages.PageName, Sections.SectionName
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Element_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Element_Delete  Script Date: 19/07/2004 ******/
CREATE PROCEDURE [dbo].[Element_Delete]
(

@SectionID Int,
@SectionElementID Int,
@SectionElementType varchar(50),
@SectionVersionID Int,
@ContentID int
)

AS
 SET NOCOUNT ON

/** Check if the Element already exist **/
if exists(

select SectionElements.SectionelementID
From SectionElements
WHERE     (SectionElements.SectionID =@SectionID) AND (SectionElements.SectionelementID = @SectionelementID)
AND (SectionElements.SectionVersion = @SectionVersionID)
AND (SectionElementType=@SectionElementType)
)

	
 	DELETE
	SectionElements

	WHERE     (SectionElements.SectionID =@SectionID) AND (SectionElements.SectionelementID = @SectionelementID)
	AND (SectionElements.SectionVersion = @SectionVersionID)
	AND (SectionElementType=@SectionElementType)' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Versions_GetListBySection]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Versions_GetListBySection   Script Date: 10/11/2003 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Versions_GetListBySection]
(

@SectionID Int,
@Editor int
)

AS
	SET NOCOUNT ON
	SELECT DISTINCT Versions.VersionId, CONVERT(varchar, Versions.Created, 103) AS Datecreation, Versions.Status, Account.UserName
FROM         Versions INNER JOIN
                      SectionElements ON Versions.SectionId = SectionElements.SectionID INNER JOIN
                      Account ON SectionElements.Author = Account.AccountID
WHERE   SectionElements.SectionId = @SectionID
And SectionElements.Author=@Editor
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sections_GetElements]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Sections_GetTemplateId   Script Date: 11/06/2004 ******/
CREATE PROCEDURE [dbo].[Sections_GetElements]
(

@SectionID Int,
@SectionElementID Int,
@VersionID int,
@SectionelementType varchar(50)
)

AS
	SET NOCOUNT ON
	
 SELECT     Types_params.TypeValue, Content.Body, Content.Url, Content.Headline, Content.FriendlyName, Content.Status,Account.UserName, SectionElements.ContentId
FROM         SectionElements INNER JOIN
                      Content ON SectionElements.ContentId = Content.ContentID INNER JOIN
                      Types_params ON Content.TypeID = Types_params.TypeId INNER JOIN
                      Account ON Content.Writer = Account.AccountID
WHERE     (SectionElements.SectionID =@SectionID) AND (SectionElements.SectionelementID = @SectionelementID)
 AND (SectionElements.Sectionversion = @VersionID)
 AND (SectionElements.SectionelementType = @SectionelementType)

	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Pages_Deploy]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Pages_Deploy] 
(
@Status int,
@PageId int,
@ServerId Int
)
AS
UPDATE Versions
	SET   Status       = @Status
	WHERE  PageID = @PageId AND Status=32
/** Delete any previous version on the selected server
 NOTE:an update will surely be more efficient but the Delete Insert method is faster
v 2.0 TextOnly field added for accessibility
**/
Delete SectionContent
where sectionId in (
SELECT     Versions.SectionId
FROM         Versions INNER JOIN
                      Sections ON Versions.SectionId = Sections.SectionId CROSS JOIN
                      Servers
WHERE     (Versions.PageId = @PageId) AND Versions.Status=@Status AND (Servers.Id = @ServerId)
)  AND ServerId=@ServerId  
/** Insert the new versions for the page selected in the selected server **/
Insert INTO SectionContent
SELECT     Versions.SectionId,Servers.Id,  Versions.SectionContent, Versions.SectionContent_Lang2, Versions.TextOnly, (getdate())
FROM         Versions INNER JOIN
                      Sections ON Versions.SectionId = Sections.SectionId CROSS JOIN
                      Servers
WHERE     (Versions.PageId = @PageId) AND (Servers.Id = @ServerId) AND (Versions.Status=@Status)' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_GetContentByType]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetContentbytype    Script Date: 13/06/2004  ******/
CREATE PROCEDURE [dbo].[Content_GetContentByType]
	(
		@TypeValue varchar(50),
		@ProjectId int,
@Startdate Datetime,
@EndDate Datetime,
@Author  Int
	)
AS
	SET NOCOUNT ON
	
 SELECT     Content.ContentID, Content.Headline, Content.FriendlyName,Content.Url,Content.PageID, Content.SectionID,Content.ProjectID, Content.ModifiedDate,
 Content.CreationDate, Account.UserName, Content.Status, Types_params.TypeValue
FROM         Content INNER JOIN
                      Types_params ON Content.TypeID = Types_params.TypeId INNER JOIN
                      Account ON Content.Writer = Account.AccountID AND Content.Writer = Account.AccountID
WHERE     Types_params.TypeValue =@TypeValue AND ProjectId=@ProjectId
AND (Content.ModifiedDate BETWEEN @StartDate AND @EndDate) AND Content.Writer=@Author
ORDER by ContentID
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_GetByTypeandSection]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetbytypeandSection    Script Date: 19/07/2004  ******/
CREATE PROCEDURE [dbo].[Content_GetByTypeandSection]
	(
		@ContentType varchar(50),
		@ProjectId int,

@SectionID Int,
@Startdate Datetime,
@EndDate Datetime
	)
AS
	SET NOCOUNT ON
	
 SELECT     Content.ContentID, Content.Headline, Content.FriendlyName,Content.Url,Content.PageID, Content.SectionID,Content.ProjectID, Content.ModifiedDate,
 Content.CreationDate, Account.UserName, Content.Status, Types_params.TypeValue
FROM         Content INNER JOIN
                      Types_params ON Content.TypeID = Types_params.TypeId INNER JOIN
                      Account ON Content.Writer = Account.AccountID AND Content.Writer = Account.AccountID
WHERE     Types_params.TypeValue =@ContentType AND ProjectId=@ProjectId AND Content.SectionID=@SectionId
AND (Content.ModifiedDate BETWEEN @StartDate AND @EndDate) 
ORDER by ContentID
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_GetBySection]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetContentBySection   Script Date: 18/07/2004  ******/
CREATE PROCEDURE [dbo].[Content_GetBySection]
	(
		@ProjectId int,
@SectionId int,
@Startdate Datetime,
@EndDate Datetime
	)
AS
	SET NOCOUNT ON
	
 SELECT     Content.ContentID, Content.Headline, Content.FriendlyName,Content.Url,Content.PageID, Content.SectionID,Content.ProjectID, Content.ModifiedDate,
 Content.CreationDate, Account.UserName, Content.Status, Types_params.TypeValue, Content.Editor
FROM         Content INNER JOIN
                      Types_params ON Content.TypeID = Types_params.TypeId INNER JOIN
                      Account ON Content.Writer = Account.AccountID
WHERE   ProjectId=@ProjectId AND Content.SectionID=@SectionID
AND (Content.ModifiedDate BETWEEN @StartDate AND @EndDate)
	
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_GetByType]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetContentByType  Script Date: 19/07/2004  ******/
CREATE PROCEDURE [dbo].[Content_GetByType]
	(
		@ProjectId int,
@Startdate Datetime,
@EndDate Datetime,
@ContentType varchar(50)
	)
AS
	SET NOCOUNT ON
	
 SELECT     Content.ContentID, Content.Headline, Content.FriendlyName,Content.Url,Content.PageID, Content.SectionID,Content.ProjectID, Content.ModifiedDate,
 Content.CreationDate, Account.UserName, Content.Status, Types_params.TypeValue, Content.Editor
FROM         Content INNER JOIN
                      Types_params ON Content.TypeID = Types_params.TypeId INNER JOIN
                      Account ON Content.Writer = Account.AccountID
WHERE   ProjectId=@ProjectId   AND  Types_params.TypeValue=@ContentType
AND (Content.ModifiedDate BETWEEN @StartDate AND @EndDate)
	
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_GetByDate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetContentByDate   Script Date: 15/06/2004  ******/
CREATE PROCEDURE [dbo].[Content_GetByDate]
	(
		@ProjectId int,
@Startdate Datetime,
@EndDate Datetime
	)
AS
	SET NOCOUNT ON
	
 SELECT     Content.ContentID, Content.Headline, Content.FriendlyName,Content.Url,Content.PageID, Content.SectionID,Content.ProjectID, Content.ModifiedDate,
 Content.CreationDate, Account.UserName, Content.Status, Types_params.TypeValue, Content.Editor
FROM         Content INNER JOIN
                      Types_params ON Content.TypeID = Types_params.TypeId INNER JOIN
                      Account ON Content.Writer = Account.AccountID
WHERE   ProjectId=@ProjectId
AND (Content.ModifiedDate BETWEEN @StartDate AND @EndDate)
	
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_GetContent]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetContent   Script Date: 14/06/2004  ******/
CREATE PROCEDURE [dbo].[Content_GetContent]
	(
		@TypeValue varchar(50),
		@ProjectId int,
@Startdate Datetime,
@EndDate Datetime
	)
AS
	SET NOCOUNT ON
	
 SELECT     Content.ContentID, Content.Headline, Content.FriendlyName,Content.Url,Content.PageID, Content.SectionID,Content.ProjectID, Content.ModifiedDate,
 Content.CreationDate, Account.UserName, Content.Status, Types_params.TypeValue
FROM         Content INNER JOIN
                      Types_params ON Content.TypeID = Types_params.TypeId INNER JOIN
                      Account ON Content.Writer = Account.AccountID
WHERE     Types_params.TypeValue =@TypeValue AND ProjectId=@ProjectId
AND (Content.ModifiedDate BETWEEN @StartDate AND @EndDate)
	
	RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_GetByAuthor]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetHeadlinesForAuthor    Script Date:15/06/2004 ******/
CREATE PROCEDURE [dbo].[Content_GetByAuthor]
	(
		@Writer INT            = NULL,
		@ProjectID INT = NULL
	)
AS
	
   SELECT     Content.ContentID, Content.Version, Content.Headline, Content.Status, Content.Approver, Types_params.TypeValue, Account.UserName, 
                      Content.ModifiedDate, Content.CreationDate, Content.Url, Content.Body
FROM         Content INNER JOIN
                      Types_params ON Content.TypeID = Types_params.TypeId INNER JOIN
                      Account ON Content.Writer = Account.AccountID

       WHERE Content.Writer=@Writer  and  Content.ProjectId=@ProjectId
	
	order by ContentID DESC

	RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content_GetContentForID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetContentForID    Script Date: 19/07/2006 ******/
CREATE PROCEDURE [dbo].[Content_GetContentForID]
	(
		@contentid INT = NULL
	)
AS
	SET NOCOUNT ON
	
SELECT     Content.*, Account.UserName, Types_params.TypeValue
FROM         Content INNER JOIN
                      Types_params ON Content.TypeID = Types_params.TypeId INNER JOIN
                      Account ON Content.Writer = Account.AccountID
    
    WHERE ContentID=@contentid
	
	RETURN' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Image_UpdateProperties]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Image_UpdateProperties] ( 


@ImageId int,
@Name varchar(200),
@Credits varchar(500),
@ShortDescription varchar(500),
@Description text,
@BroadcastDate smalldatetime,
@Duration varchar(50),
@AltText  varchar(200),
@MediaUrl varchar(300),
@Relevance varchar(1000),
@MediaType int,
@Language int,
@VideoLanguage varchar(100)
)
AS 

Select *  into #tmp from Library where ImageId=@imageId

If not(Exists(Select ImageId from Library where ImageId=@ImageId and Language=@Language))

Begin


Update #tmp set Language=1,
ImageName=@Name,
Credits=@Credits,
MediaType=@MediaType,
ShortDescription=@ShortDescription,
[Description]=@Description,
BroadcastDate=@BroadcastDate,
Duration=@Duration,
AltText=@AltText,
MediaUrl=@MediaUrl,
VideoLanguage=@VideoLanguage,
Relevance=@Relevance

INSERT INTO Library
Select * from #tmp
where ImageId=@ImageId

End

Update Library

Set
ImageName=@Name,
Credits=@Credits,
MediaType=@MediaType,
ShortDescription=@ShortDescription,
[Description]=@Description,
BroadcastDate=@BroadcastDate,
Duration=@Duration,
AltText=@AltText,
MediaUrl=@MediaUrl,
VideoLanguage=@VideoLanguage,
Relevance=@Relevance
where ImageId=@ImageId and Language=@Language' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Image_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Image_Update] ( 
@img_name [varchar](50),
@img_data [image], 
@img_contenttype [varchar](50),
@img_thumbnail [image],
@imgWidth int,
@imgHeight int,
@AltText  varchar(50),
@ImageId int)
AS 
begin transaction 


 update Library 
SET 
ImageData=@img_data,
ImageName=@img_name,
AltText= @AltText,
ContentType=@img_contenttype,
Thumbnail=@img_thumbnail,
Width=@imgWidth,
Height=@imgHeight

WHERE ImageId=@ImageId and language=0
commit transaction' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Image_GetbyId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE  [dbo].[Image_GetbyId] 
(
@ImageId Int
)
AS
select ImageData
from Library
where ImageId=@ImageId' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Image_Insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Image_Insert] ( 
@ProjectID [int], 
@img_name [varchar](50),
@img_data [image], 
@img_thumbnail [image], 
@img_contenttype [varchar](50),
@imgWidth int,
@imgHeight int,
@AltText  varchar(50),
@Editor int,
@FolderId int
)
AS 
begin transaction 
DECLARE @ImageId int

SET @ImageID =  ( SELECT DISTINCT TOP 1 ImageId 
    FROM     Library
    ORDER BY ImageId DESC)+1

  INSERT INTO Library ( 
ImageId,
ProjectId,
FolderId,
ImageData,
Thumbnail,
ImageName,
AltText,
ContentType,
Width,
Height,
Editor
 )
VALUES ( 
@ImageId,
@ProjectId,
@FolderId,
  @img_data, 
@img_thumbnail,
  @img_name, 
  @AltText,
  @img_contenttype,
@imgWidth,
@imgHeight,
@Editor
)
commit transaction' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Template_InsertCode]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Template_InsertCode]
(
@TemplateName varchar(50),
@ProjectId int,
@TemplateCode text,
@thisId int OUTPUT
)
AS 
INSERT  INTO Templates
(TemplateName, Project, TemplateCode) 
VALUES
(@TemplateName, @ProjectId,@TemplateCode)
;
 SELECT @thisId=SCOPE_IDENTITY() FROM Templates' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Template_GetById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/****** Object:  Stored Procedure dbo.Content_GetHeadlines    Script Date: 3/9/2004 2:36:38 AM ******/
CREATE PROCEDURE [dbo].[Template_GetById]
(
@TemplateID Int =NULL
)
AS
	SET NOCOUNT ON
	
SELECT     Template_Elts.Type_T_Elt + '' '' + CAST(Template_Elts.TemplateEltId AS varchar(50)) AS TemplateName, TemplateEltId, Type_T_elt, EltOrder, eltCode
FROM         Templates INNER JOIN
                      Template_Elts ON Templates.TemplateId = Template_Elts.TemplateId
WHERE    Templates.TemplateId= @TemplateID
	RETURN' 
END
